package cn.gzmli.dtt.common.service;

import cn.gzmli.dtt.common.dao.MessageDao;
import cn.gzmli.dtt.common.entity.Message;
import cn.gzmli.dtt.common.entity.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;

/**
 * 消息服务类
 * @author wonderful
 * @create 20160620
 */
@Service
@Transactional
public class MessageService {

	@Autowired
	private MessageDao messageDao;
	
	/**
	 * 插入单条消息记录针对发送给一个用户
	 * @param message
	 * @return 当前添加的消息记录ID
	 * @throws Exception
	 */
	public int insertMessage(Message message, Integer userID) throws Exception {
		String sql=String.format("INSERT INTO  Message (logID,title,content,messageType,source,sourceID,senderID) "
				+ " VALUES(%d,'%s','%s','%s',%d,'%s',%d)",
				message.getLogID()==null?null:message.getLogID(),
				message.getTitle()==null?"":message.getTitle(),
			    message.getContent()==null?"":message.getContent(),
			    message.getMessageType()==null?null:message.getMessageType(),
			    message.getSource()==null?0:message.getSource(),
			    message.getSourceID()==null?null:message.getSourceID(),
			    message.getSenderID()==null?null:message.getSenderID()
				);
		messageDao.getJdbcTemplate().execute(sql);
		String queryLastMessageIDSql="SELECT LAST_INSERT_ID()";
		int messageID=messageDao.getJdbcTemplate().queryForObject(queryLastMessageIDSql, Integer.class);
		String insertMessageUser=String.format("INSERT INTO MessageUser (messageID,userID) VALUES(%d,%d)",
				messageID,userID
				);
		messageDao.getJdbcTemplate().execute(insertMessageUser);
		return messageID;
	}

	/**
	 * 插入单条消息记录针对发送给一个用户
	 * @param message
	 * @return 当前添加的消息记录ID
	 * @throws Exception
	 */
	public int addUserMessage(Message message, Integer userID, Integer messageID) throws Exception {
		if(messageID==null){
			String sql=String.format("INSERT INTO  Message (logID,title,content,messageType,source,sourceID,senderID) "
							+ " VALUES(%d,'%s','%s','%s',%d,'%s',%d)",
					message.getLogID()==null?null:message.getLogID(),
					message.getTitle()==null?"":message.getTitle(),
					message.getContent()==null?"":message.getContent(),
					message.getMessageType()==null?null:message.getMessageType(),
					message.getSource()==null?0:message.getSource(),
					message.getSourceID()==null?null:message.getSourceID(),
					message.getSenderID()==null?null:message.getSenderID()
			);
			messageDao.getJdbcTemplate().execute(sql);
			String queryLastMessageIDSql="SELECT LAST_INSERT_ID()";
			messageID=messageDao.getJdbcTemplate().queryForObject(queryLastMessageIDSql, Integer.class);
		}
		String insertMessageUser=String.format("INSERT INTO MessageUser (messageID,userID) VALUES(%d,%d)",
				messageID,userID
		);
		messageDao.getJdbcTemplate().execute(insertMessageUser);
		return messageID;
	}
	
	/**
	 * 插入多条消息记录针对多个用户
	 * @param message
	 * @param userIDS
	 * @throws Exception
	 */
	public void insertMessage(Message message,String userIDS)throws Exception {
		
	}
	
	/**
	 * 更新消息记录
	 * @param message
	 * @throws Exception
	 */
	public void updateMessage(Message message)throws Exception {
	  messageDao.update(message);
	}
	
	/**
	 * 更新消息记录
	 * @param messageID
	 * @param userID
	 * @throws Exception
	 */
	public void updateMessageStatus(Integer messageID, Integer userID)throws Exception {
	  String sql=String.format("UPDATE MessageUser SET status=1 WHERE userID=%d AND messageID=%d ",userID,messageID);
	  messageDao.getJdbcTemplate().execute(sql);
	}
	
	/**
	 * 按条件获取消息记录集合
	 * @param condition
	 * @return
	 * @throws Exception
	 */
	public List<Message> queryMsg(Map<String,String> condition)throws Exception {
		
		return messageDao.queryEntity(condition);
		
	}
	
	/**
	 * 按条件获取消息记录集合
	 * @param userID
	 * @param title
	 * @param messageType
	 * @param status
	 * @param sourceID
	 * @param sendBeginTime
	 * @param sendEndTime
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<Map<String,Object>> queryMsg(
			Integer userID,
			String title,
			String messageType,
			Integer status,
			String sourceID,
			String sendBeginTime,
			String sendEndTime,
			PageInfo pageInfo)throws Exception {


	String sql=String.format("SELECT "
			+ "m.id id,"//消息id
			+ "m.logID logID,"//消息关联的任务记录id
			+ "m.title title,"//消息标题
			+ "m.content content,"//消息内容
			+ "m.messageType messageType,"//消息类型
			+ "mu.status status,"//消息状态
			+ "m.source source,"//消息来源
			+ "m.sourceID sourceID,"//消息来源id
			+ "DATE_FORMAT(m.createTime,'%%Y-%%m-%%d %%H:%%i:%%s') createTime,"//记录生成时间
			+ "receiver.id receiverID,"//接收人id
			+ "receiver.name receiverName,"
//			+ "receiver.headPortrait,"//接收人头像
			+ "sender.id senderID,"//发送人id
			+ "sender.name senderName "//发送人姓名
//			+ "sender.headPortrait "//发送人头像
			+ "FROM Message m "
			+ "INNER JOIN MessageUser mu ON m.id=mu.messageID "
			+ "LEFT JOIN User receiver ON receiver.id=mu.userID "
			+ "LEFT JOIN User sender ON sender.id=m.senderID "
			+ "WHERE  mu.userID=%d "
			,userID);
	
	if(status!=null&&status!=99){
		sql+=" AND mu.status="+status+" ";
	}
	if(messageType!=null&&!messageType.equals("")){
		sql+=" AND m.messageType='"+messageType+"'  ";
	}
	if(sendBeginTime!=null&&!sendBeginTime.equals("")){
		sql+=" AND m.createTime>='"+sendBeginTime+"' ";
	}
	if(sendEndTime!=null&&!sendEndTime.equals("")){
		sql+=" AND m.createTime <='"+sendEndTime+"' ";
	}
	if(title!=null&&!title.equals("")){
		sql+=" AND m.title LIKE '%"+title+"%' ";
	}
	if(sourceID!=null&&!sourceID.equals("")){
		sql+=" AND m.sourceID ='"+sourceID+"' ";
	}
	
	sql+=" ORDER BY m.id DESC ";
	sql+=" LIMIT "+pageInfo.getStartRow()+","+pageInfo.getPageSize();
	String countSql=sql.replaceAll("SELECT[\\W\\w]+FROM","SELECT COUNT(*) totalRows FROM");
	countSql=countSql.replaceAll("LIMIT [\\W\\w]+","");
    pageInfo.setTotalRows(messageDao.getJdbcTemplate().queryForObject(countSql, Integer.class));
    pageInfo.createTotalPages();
	return messageDao.getJdbcTemplate().queryForList(sql);
		
		
	}
   
	/**
	 * 获取用户未读消息数量
	 * @param userID
	 * @return
	 * @throws Exception
	 */
   public int queryUnReadMsgCount(Integer userID)throws Exception {
		String sql=String.format("SELECT COUNT(*) FROM Message m,MessageUser mu WHERE mu.userID=%d AND mu.messageID=m.id AND mu.status=0 ",userID);
		return messageDao.getJdbcTemplate().queryForObject(sql, Integer.class);
	}
   
   /**
	 * 获取消息状态
	 * @param tIDS
	 * @return
	 * @throws Exception
	 */
	public List<Map<String,Object>> selectMsgsStatus(
			String tIDS
			)throws Exception {


		String sql=String.format("SELECT logID tID,status FROM Message WHERE logID IN (%s) ",tIDS);
		return messageDao.getJdbcTemplate().queryForList(sql);


	}
	
	/**
	 * 获取消息状态
	 * @param tIDS
	 * @return
	 * @throws Exception
	 */
	public void updateMsgsStatus(
			String tIDS
			)throws Exception {


		String sql=String.format("UPDATE Message SET status=1 WHERE logID IN(%s)",tIDS);
		messageDao.getJdbcTemplate().execute(sql);


	}

	
}
